package com.imooc.utils;

import com.google.common.collect.Maps;
import com.imooc.mapper.CompareColumnMapper;
import com.imooc.mapper.GoodsInfoMapper;
import com.imooc.pojo.CompareColumn;
import com.imooc.pojo.GoodsInfo;
import com.imooc.pojo.SaveModel;
import com.imooc.pojo.UploadGoodsInfo;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.util.*;

@Component
public class ExcelUtils {
    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    @Autowired
    private GoodsInfoMapper goodsInfoMapper;

    @Autowired
    private CompareColumnMapper compareColumnMapper;

    public void excelToShopIdList(HttpServletResponse response, InputStream inputStream) {
        List<Object> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum();
            System.out.println("总行数有多少行" + rowLength);
            //工作表的列
            Row row = sheet.getRow(0);

            //总列数
            int colLength = row.getLastCellNum();
            System.out.println("总列数有多少列" + colLength);
            //得到指定的单元格
            Cell cell = row.getCell(0);

            List<GoodsInfo> goodsInfos = goodsInfoMapper.selectAllInfo(null);
            List<UploadGoodsInfo> sheet1List = new ArrayList<>();
            List<UploadGoodsInfo> sheet2List = new ArrayList<>();
            UploadGoodsInfo uploadGoodsInfo = null;
            boolean flag = false;
            for (int i = 1; i <= rowLength; i++) {
                uploadGoodsInfo = new UploadGoodsInfo();
                row = sheet.getRow(i);
                if (StringUtils.isEmpty(getCellValue(row, 4))){
                    continue;
                }
                if (StringUtils.isEmpty(getCellValue(row, 5))){
                    continue;
                }
                if (StringUtils.isEmpty(getCellValue(row, 6))){
                    continue;
                }
                uploadGoodsInfo.setGoodsId(getCellValue(row, 0));
                uploadGoodsInfo.setGoodsTypeId(getCellValue(row, 1));
                uploadGoodsInfo.setGoodsName(getCellValue(row, 2));
                uploadGoodsInfo.setExpectSale(getCellValue(row, 3));
                uploadGoodsInfo.setPrice(getCellValue(row, 4));
                uploadGoodsInfo.setDiscount(getCellValue(row, 5));
                uploadGoodsInfo.setExpress(getCellValue(row, 6));
                uploadGoodsInfo.setStock(getCellValue(row, 7));
                uploadGoodsInfo.setSeller(getCellValue(row, 8));
                uploadGoodsInfo.setSellerExpress(getCellValue(row, 9));
                uploadGoodsInfo.setSellerPrice(getCellValue(row, 10));

                if (null != goodsInfos) {
                    for (GoodsInfo goodsInfo : goodsInfos) {
                        if(StringUtils.isNoneBlank(goodsInfo.getTotalPrice())) {
                            uploadGoodsInfo.setTotalPrice(goodsInfo.getTotalPrice());
                        }
                        if (uploadGoodsInfo.getGoodsTypeId().equals(goodsInfo.getGoodsTypeId())) {
                            if (Integer.parseInt(goodsInfo.getTotalPrice()) < (Integer.parseInt(uploadGoodsInfo.getPrice()) + Integer.parseInt(uploadGoodsInfo.getExpress().replaceAll("\\+", "")))) {
                                sheet1List.add(uploadGoodsInfo);
                                flag = true;
                            }
                        }
                    }
                }
                if (!flag) {

                    sheet2List.add(uploadGoodsInfo);
                } else {
                    flag = false;
                }
                uploadGoodsInfo = null;
            }
            Collections.sort(sheet2List);
            expoerDataExcel(response, sheet1List, sheet2List);
        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.error("parse excel file error :", e);
        }

    }

    public void expoerDataExcel(HttpServletResponse response, List<UploadGoodsInfo> sheet1List, List<UploadGoodsInfo> sheet2List) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        //输出xls文件名称
        String xlsFile_name = System.currentTimeMillis() + ".xlsx";
        //内存中只创建100个对象
        //关键语句
        Workbook wb = new SXSSFWorkbook(100);
        //工作表对象
        Sheet sheet = null;
        //行对象
        Row nRow = null;
        //列对象
        Cell nCell = null;
        //总行号
        int rowNo = 0;
        //页行号
        int pageRowNo = 0;

        String title[] = {"노출상품ID", "옵션ID", "상품명", "예상 매출 금액", "아이템위너 판매가격(원)",
                "아이템위너 쿠폰(원)", "아이템위너 배송비(원)", "판매자 판매가격(원)", "판매자 쿠폰(원)", "판매자 배송비(원)", "변경 후 판매가격(원)"};
        //创建新的sheet对象
        wb.createSheet("对比商品");
        //动态指定当前的工作表
        sheet = wb.getSheetAt(0);
        //新建了工作表,重置工作表的行号为0
        pageRowNo = 0;
        // -----------定义表头-----------
        nRow = sheet.createRow(0);
        // 列数 titleKeyList.size()
        for (int i = 0; i < title.length; i++) {
            Cell cell_tem = nRow.createCell(i);
            cell_tem.setCellValue(title[i]);
        }
        setSheetDate(sheet, sheet1List);

        //创建新的sheet对象
        wb.createSheet("删除商品信息");
        //动态指定当前的工作表
        Sheet sheet2 = wb.getSheetAt(1);
        //新建了工作表,重置工作表的行号为0
        pageRowNo = 0;
        // -----------定义表头-----------
        nRow = sheet2.createRow(0);
        // 列数 titleKeyList.size()
        for (int i = 0; i < title.length; i++) {
            Cell cell_tem = nRow.createCell(i);
            cell_tem.setCellValue(title[i]);
        }
        setSheetDate(sheet2, sheet2List);

        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

    private void setSheetDate(Sheet sheet, List<UploadGoodsInfo> sheet1List) {
        for (int i = 0; i < sheet1List.size(); i++) {
            Row row = sheet.createRow(i+1);
            row.createCell(0).setCellValue(sheet1List.get(i).getGoodsId());
            row.createCell(1).setCellValue(sheet1List.get(i).getGoodsTypeId());
            row.createCell(2).setCellValue(sheet1List.get(i).getGoodsName());
            row.createCell(3).setCellValue(sheet1List.get(i).getExpectSale());
            row.createCell(4).setCellValue(sheet1List.get(i).getPrice());
            row.createCell(5).setCellValue(sheet1List.get(i).getDiscount());
            row.createCell(6).setCellValue(sheet1List.get(i).getExpress());
            row.createCell(7).setCellValue(sheet1List.get(i).getStock());
            row.createCell(8).setCellValue(sheet1List.get(i).getSeller());
            row.createCell(9).setCellValue(sheet1List.get(i).getSellerExpress());
            row.createCell(10).setCellValue(sheet1List.get(i).getSellerPrice());
        }
    }



    public void saveImportGoods(InputStream inputStream) {
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum();
            System.out.println("总行数有多少行" + rowLength);
            //工作表的列
            Row row = sheet.getRow(0);

            //总列数
            int colLength = row.getLastCellNum();
            System.out.println("总列数有多少列" + colLength);

            GoodsInfo uploadGoodsInfo = null;
            GoodsInfo exitsGoodsInfo = null;
            for (int i = 0; i <= rowLength; i++) {
                row = sheet.getRow(i);
                exitsGoodsInfo = goodsInfoMapper.getGoodsInfoByTypeId(getCellValue(row, 1));
                if (exitsGoodsInfo == null) {
                    uploadGoodsInfo = new GoodsInfo();
                    uploadGoodsInfo.setGoodsId(getCellValue(row, 0));
                    uploadGoodsInfo.setGoodsTypeId(getCellValue(row, 1));
//                uploadGoodsInfo.setGoodsStatus(getCellValue(row, 2));
//                uploadGoodsInfo.setGoodsType(getCellValue(row, 3));
//                uploadGoodsInfo.setPrice(getCellValue(row, 4));
                    uploadGoodsInfo.setStock(getCellValue(row, 2));
//                uploadGoodsInfo.setPromote(getCellValue(row, 6));
                    goodsInfoMapper.saveGoodsInfo(uploadGoodsInfo);
                }
                exitsGoodsInfo = null;
                uploadGoodsInfo = null;
            }
        } catch (Exception e) {
            LOGGER.error("parse excel file error :", e);
        }

    }
	/**
     * 将inputStream转化为file
     * @param is
     * @param file 要输出的文件目录
     */
    public static void inputStream2File(InputStream is, File file) throws IOException {
        OutputStream os = null;
        try {
            os = new FileOutputStream(file);
            int len = 0;
            byte[] buffer = new byte[8192];

            while ((len = is.read(buffer)) != -1) {
                os.write(buffer, 0, len);
            }
        }catch (IOException e) {
            LOGGER.error("上传文件失败{}", e);
        }finally {
            os.close();
            is.close();
        }
    }
    public void saveImportGoodsCSV(InputStream inputStream) {
        File csvFile = new File("upload.csv");
        try {
            if (!csvFile.exists()) {
                csvFile.createNewFile();
            }
            inputStream2File(inputStream, csvFile);
            goodsInfoMapper.loadFile(csvFile.getAbsolutePath());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    @Transactional(rollbackFor=Exception.class)
    public void saveImportCompare(InputStream inputStream) {
        Workbook workbook = null;
        try {
            // 清空数据表
            compareColumnMapper.delAllData();
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum();
            System.out.println("总行数有多少行" + rowLength);
            //工作表的列
            Row row = sheet.getRow(0);

            //总列数
            int colLength = row.getLastCellNum();
            System.out.println("总列数有多少列" + colLength);

            CompareColumn compareColumn = null;
            for (int i = 0; i <= rowLength; i++) {
                compareColumn = new CompareColumn();
                row = sheet.getRow(i);
                compareColumn.setMyCol(getCellValue(row, 0));
                compareColumn.setOrgCol(getCellValue(row, 1));

                compareColumnMapper.saveCompareColumn(compareColumn);
                compareColumn = null;
            }
        } catch (Exception e) {
            LOGGER.error("parse excel file error :", e);
        }

    }

    private String getCellValue(Row row, int i){
        if (row.getCell(i).getCellTypeEnum()== CellType.NUMERIC){
            return String.valueOf(row.getCell(i).getNumericCellValue());
        } else {
            return row.getCell(i).getStringCellValue();
        }
    }


    /**
     * 通用导出 （单sheet）
     * @param response 导出相应文件流
     * @param map 查询数据参数
     * @throws IOException
     */
    public void exportGoodsInfoData(HttpServletResponse response, Map<String, Object> map) throws IOException{
        List<GoodsInfo> sheetList = goodsInfoMapper.selectAllInfo(map);
        //输出xls文件名称
        String xlsFile_name = System.currentTimeMillis() + ".xlsx";
        //内存中只创建100个对象
        //关键语句
        Workbook wb = new SXSSFWorkbook(100);
        //工作表对象
        Sheet sheet = null;
        //行对象
        Row nRow = null;
        //列对象
        Cell nCell = null;
        //总行号
        int rowNo = 0;
        //页行号
        int pageRowNo = 0;

        //创建新的sheet对象
        wb.createSheet("工作簿1");
        //动态指定当前的工作表
        sheet = wb.getSheetAt(0);
        //新建了工作表,重置工作表的行号为0
        pageRowNo = 0;
        // -----------定义表头-----------
        nRow = sheet.createRow(0);
        // 列数 titleKeyList.size()
        for (int i = 0; i < sheetList.size(); i++) {
            Row row = sheet.createRow(i+1);
            row.createCell(0).setCellValue(sheetList.get(i).getGoodsId());
            row.createCell(1).setCellValue(sheetList.get(i).getGoodsTypeId());
            row.createCell(2).setCellValue(sheetList.get(i).getStock());
        }

        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

    /**
     * 导出汇率价格信息
     * @param response
     * @param map
     * @throws IOException
     */
    public void exportCompareInfoData(HttpServletResponse response, Map<String, Object> map) throws IOException {
        List<CompareColumn> sheetList = compareColumnMapper.getAllCompareColumn(map);
        //输出xls文件名称
        String xlsFile_name = System.currentTimeMillis() + ".xlsx";
        //内存中只创建100个对象
        //关键语句
        Workbook wb = new SXSSFWorkbook(100);
        //工作表对象
        Sheet sheet = null;
        //行对象
        Row nRow = null;
        //列对象
        Cell nCell = null;
        //总行号
        int rowNo = 0;
        //页行号
        int pageRowNo = 0;

        //创建新的sheet对象
        wb.createSheet("工作簿1");
        //动态指定当前的工作表
        sheet = wb.getSheetAt(0);
        //新建了工作表,重置工作表的行号为0
        pageRowNo = 0;
        // -----------定义表头-----------
        nRow = sheet.createRow(0);
        // 列数 titleKeyList.size()
        for (int i = 0; i < sheetList.size(); i++) {
            Row row = sheet.createRow(i+1);
            row.createCell(0).setCellValue(sheetList.get(i).getMyCol());
            row.createCell(1).setCellValue(sheetList.get(i).getOrgCol());
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

    /**
     * 导出爬取文件
     * @param response
     * @param sheetList
     * @throws IOException
     */
    public void exportInfo(HttpServletResponse response, List<SaveModel> sheetList) throws IOException {
        //输出xls文件名称
        String xlsFile_name = System.currentTimeMillis() + ".xlsx";
        //内存中只创建100个对象
        //关键语句
        Workbook wb = new SXSSFWorkbook(100);
        //工作表对象
        Sheet sheet = null;
        //行对象
        Row nRow = null;
        //列对象
        Cell nCell = null;
        //总行号
        int rowNo = 0;
        //页行号
        int pageRowNo = 0;
//        Row row1 = sheet.createRow(0);
        // 生成样式
//        Map<String, CellStyle> styles = createStyles(workbook);
        String[] titles = {"编号","链接地址", "商品名", "尺寸", "颜色",  "价格"};
//        Map<String, Integer> titleOrder = Maps.newHashMap();
//        for (int i = 0; i < titles.length; i++) {
//            Cell cell = row1.createCell(i);
////            cell.setCellStyle(styles.get("header"));
//            String title = titles[i];
//            cell.setCellValue(title);
//            titleOrder.put(title, i);
//        }

        //创建新的sheet对象
        wb.createSheet("工作簿1");
        //动态指定当前的工作表
        sheet = wb.getSheetAt(0);
        //新建了工作表,重置工作表的行号为0
        pageRowNo = 0;
        // -----------定义表头-----------
        nRow = sheet.createRow(0);

        Map<String, Integer> titleOrder = Maps.newHashMap();
        for (int i = 0; i < titles.length; i++) {
            Cell cell = nRow.createCell(i);
            String title = titles[i];
            cell.setCellValue(title);
            titleOrder.put(title, i);
        }
        // 列数 titleKeyList.size()
        for (int i = 0; i < sheetList.size(); i++) {
            Row row = sheet.createRow(i);
            setCellValue(sheetList, i, row);
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();
    }

    private void setCellValue(List<SaveModel> sheetList, int i, Row row) {
        row.createCell(0).setCellValue(sheetList.get(i).getId());
        row.createCell(1).setCellValue(sheetList.get(i).getUrl());
        row.createCell(2).setCellValue(sheetList.get(i).getName());
        row.createCell(3).setCellValue(sheetList.get(i).getSize());
        row.createCell(4).setCellValue(sheetList.get(i).getColor());
        row.createCell(5).setCellValue(sheetList.get(i).getPrice());
    }


    String[] titles = {"编号","链接地址", "商品名", "尺寸", "颜色",  "价格"};
    /**
     * 创建Excel文件
     * @param filepath filepath 文件全路径
     * @param sheetName 新Sheet页的名字
     * @param values 每行的单元格
     */
    public void writeExcel(String filepath, String sheetName,
                                     List<SaveModel> values) throws IOException {
        OutputStream outputStream = null;
        if (StringUtils.isBlank(filepath)) {
            throw new IllegalArgumentException("文件路径不能为空");
        } else {
            String suffiex = "xlsx";
//            if (StringUtils.isBlank(suffiex)) {
//                throw new IllegalArgumentException("文件后缀不能为空");
//            }
            Workbook workbook;
            if ("xls".equals(suffiex.toLowerCase())) {
                workbook = new HSSFWorkbook();
            } else {
                workbook = new XSSFWorkbook();
            }
            // 生成一个表格
            Sheet sheet;
            if (StringUtils.isBlank(sheetName)) {
                // name 为空则使用默认值
                sheet = workbook.createSheet();
            } else {
                sheet = workbook.createSheet(sheetName);
            }
            // 设置表格默认列宽度为15个字节
            sheet.setDefaultColumnWidth((short) 15);
            // 生成样式
//            Map<String, CellStyle> styles = createStyles(workbook);
            // 创建标题行
            Row row = sheet.createRow(0);
            // 存储标题在Excel文件中的序号
            Map<String, Integer> titleOrder = Maps.newHashMap();
            for (int i = 0; i < titles.length; i++) {
                Cell cell = row.createCell(i);
//                cell.setCellStyle(styles.get("header"));
                String title = titles[i];
                cell.setCellValue(title);
                titleOrder.put(title, i);
            }

            // 写入正文
            for (int i = 0; i < values.size(); i++) {
                Row rows = sheet.createRow(i + 1);
                setCellValue(values, i, rows);
            }
            try {
                outputStream = new FileOutputStream(filepath);
                workbook.write(outputStream);
            } finally {
                if (outputStream != null) {
                    outputStream.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
            }
        }
    }
}
